const express = require('express')
const db = require('../utils/database')
const router = express.Router()
const logger = require('../utils/logger')
// 1. 查询包含该单词的词语
router.get('/contains/:word', (req, res) => {
  try {
    const word = req.params.word
    if (!word) {
      return res.status(400).json({ message: 'Word parameter is required' })
    }

    const query = `SELECT word FROM enwords WHERE word LIKE ? 
      AND word != ? 
      AND word not like ?
      AND word not like ?
      LIMIT 20`
    const pattern = `%${word}%`

    db.all(query, [pattern, word, `%${word}`, `${word}%`], (err, rows) => {
      if (err) {
        console.error(err)
        return res.status(500).json({ message: 'Database error' })
      }

      const words = rows.map((row) => row.word)
      res.json({ words })
    })
  } catch (error) {
    logger.error(error)
    res.status(500).json({ message: 'Server error' })
  }
})

// 2. 查询相似的单词 (基于字符串相似度)
router.get('/similar/:word', (req, res) => {
  try {
    const word = req.params.word

    if (!word) {
      return res.status(400).json({ message: 'Word parameter is required' })
    }

    // 获取所有单词
    const query = createSQLSimilar(word)

    db.all(query, [], (err, rows) => {
      if (err) {
        console.error(err)
        return res.status(500).json({ message: 'Database error' })
      }

      res.json({ words: rows })
    })
  } catch (error) {
    logger.error(error)
    res.status(500).json({ message: 'Server error' })
  }
})

// 3. 查询以该单词为开头的单词
router.get('/startswith/:word', (req, res) => {
  try {
    const word = req.params.word
    if (!word) {
      return res.status(400).json({ message: 'Word parameter is required' })
    }

    const query = `SELECT word FROM enwords WHERE word LIKE ? AND word != ? LIMIT 20`
    const pattern = `${word}%`

    db.all(query, [pattern, word], (err, rows) => {
      if (err) {
        console.error(err)
        return res.status(500).json({ message: 'Database error' })
      }

      const words = rows.map((row) => row.word)
      res.json({ words })
    })
  } catch (error) {
    logger.error(error)
    res.status(500).json({ message: 'Server error' })
  }
})

// 4. 查询以该单词结尾的单词
router.get('/endswith/:word', (req, res) => {
  try {
    const word = req.params.word
    if (!word) {
      return res.status(400).json({ message: 'Word parameter is required' })
    }

    const query = `SELECT word FROM enwords WHERE word LIKE ? AND word != ? LIMIT 20`
    const pattern = `%${word}`

    db.all(query, [pattern, word], (err, rows) => {
      if (err) {
        console.error(err)
        return res.status(500).json({ message: 'Database error' })
      }

      const words = rows.map((row) => row.word)
      res.json({ words })
    })
  } catch (error) {
    logger.error(error)
    res.status(500).json({ message: 'Server error' })
  }
})

function createSQLSimilar(theWord) {
  const length = theWord.length
  let strSQL = `
  WITH target AS (
    SELECT '${theWord}' AS target_word, ${length} AS target_len  -- 目标词及长度
  ),
  word_features AS (
    SELECT 
      a.word,
      t.target_len, -- 显式选择target_len字段
      -- 长度差异（用于计算长度接近度）
      ABS(LENGTH(a.word) - t.target_len) AS len_diff,`
  // -- 计算公共前缀长度（逐字符比较）
  for (let i = 0; i < length; i++) {
    const idx = i + 1
    strSQL += `CASE WHEN SUBSTR(a.word, ${idx}, 1) = SUBSTR(t.target_word, ${idx}, 1) THEN 1 ELSE 0 END +
    `
  }
  strSQL += ` 0 AS prefix_len,`
  // 计算公共后缀长度（逐字符比较，假设目标词长度为4）
  for (let i = 0; i < length - 1; i++) {
    const idx = i + 1
    strSQL += `CASE WHEN SUBSTR(a.word, -${idx}, 1) = SUBSTR(t.target_word, -${idx}, 1) THEN 1 ELSE 0 END +
    `
  }
  strSQL += ` 0 AS suffix_len,`
  // 字符重合度（目标词中每个字符是否在当前词中出现）
  for (let i = 0; i < length; i++) {
    strSQL += `(CASE WHEN INSTR(a.word, '${theWord[i]}') > 0 THEN 1 ELSE 0 END) +
    `
  }
  strSQL += ` 0 AS char_overlap,`

  // 字符顺序匹配度（连续字符对匹配）
  for (let i = 0; i < length - 1; i++) {
    const substr = theWord.substring(i, i + 2)
    strSQL += `(CASE WHEN INSTR(a.word, '${substr}') > 0 THEN 1 ELSE 0 END) +
    `
  }
  strSQL += ` 0 AS char_sequence_score,`

  // 字符位置匹配度（每个位置字符匹配得分）
  for (let i = 0; i < length; i++) {
    const idx = i + 1
    strSQL += `CASE WHEN SUBSTR(a.word, ${idx}, 1) = SUBSTR(t.target_word, ${idx}, 1) THEN 1 ELSE 0 END +`
  }
  strSQL += ` 0 AS char_position_match`

  strSQL += ` FROM enwords a, target t
    WHERE a.word != t.target_word  -- 排除自身
    AND a.word NOT LIKE '%' || t.target_word || '%'  -- word 不包含target_word
    AND LENGTH(a.word) <= t.target_len + 2
    AND LENGTH(a.word) >= t.target_len - 2
  ),
  score_calc AS (
    SELECT 
      word,
      -- 长度接近度得分（差异越小得分越高） 归一化到0-20分
      (CASE 
        WHEN len_diff = 0 THEN 10.0
        WHEN len_diff = 1 THEN 6.0
        WHEN len_diff = 2 THEN 5.0
        WHEN len_diff = 3 THEN -8.0
        WHEN len_diff = 4 THEN -10.0
        WHEN len_diff = 5 THEN -15.0
        ELSE -20 
      END) +
      -- 前缀得分 归一化到0-5分
      (CASE 
        WHEN target_len > 0 THEN (prefix_len * 5.0 / target_len)
        ELSE 0
      END) +
      -- 后缀得分 归一化到0-5分
      (CASE 
        WHEN target_len > 1 THEN (suffix_len * 5.0 / (target_len - 1))
        ELSE 0
      END) +
      -- 字符重合得分 归一化到0-20分
      (CASE 
        WHEN target_len > 0 THEN (char_overlap * 20.0 / target_len)
        ELSE 0
      END) +
      -- 字符顺序得分 归一化到0-20分
      (CASE 
        WHEN target_len > 1 THEN (char_sequence_score * 20.0 / (target_len - 1))
        ELSE 0
      END) +
      -- 字符位置匹配得分 归一化到0-50分
      (CASE 
        WHEN target_len > 0 THEN (char_position_match * 50.0 / target_len)
        ELSE 0
      END) AS total_score
    FROM word_features
  )
  -- 取得分最高的前10个词
  SELECT word, total_score
  FROM score_calc
  WHERE total_score > 80
  ORDER BY total_score DESC
  LIMIT 10;
  `
  return strSQL
}

// const mysql = (createSQLSimilar("just"));
// console.log(mysql);
// db.all(mysql, [], (err, rows) => {
//   if (err) {
//     console.error(err);
//   }
//   console.log(rows);
// });
module.exports = router
